package com.summary.richtext.utils.excel;

import com.summary.richtext.utils.excel.annotation.RichExcelField;
import com.summary.richtext.utils.excel.util.Reflections;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;

public class ImportExcel implements Serializable {

    /**
     * 工作薄对象
     */
    private final Workbook wb;

    /**
     * 公式转换工具(基本用于判断 Workbook 属于 HSSFWorkbook 还是 XSSFWorkbook)
     */
    private HSSFFormulaEvaluator hssEvaluator;

    private XSSFFormulaEvaluator xssEvaluator;

    /**
     * 工作表对象
     */
    private final Sheet sheet;

    /**
     * 距离数据最近的标题索引（行号-1）
     * 如：Excel第1行为标题，第2行为标题，
     * 则headerIndex为1，数据起始索引为headerIndex+1
     */
    private final int headerIndex;

    /**
     * 构造函数
     *
     * @param fileName    导入文件对象
     * @param headerIndex 距离数据最近的标题索引（行号-1）
     *                    如：Excel第1行为标题，第2行为标题，
     *                    则headerIndex为1，数据起始索引为headerIndex+1
     * @param sheetIndex  工作表编号（sheet页索引）
     * @throws IOException IOException
     */
    public ImportExcel(String fileName, InputStream is, int headerIndex, int sheetIndex) throws IOException {
        if (StringUtils.isBlank(fileName)) {
            throw new RuntimeException("导入文档为空!");
        } else if (fileName.toLowerCase().endsWith("xls")) {
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
            this.wb = hssfWorkbook;
            hssEvaluator = new HSSFFormulaEvaluator(hssfWorkbook);
        } else if (fileName.toLowerCase().endsWith("xlsx")) {
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
            this.wb = xssfWorkbook;
            xssEvaluator = new XSSFFormulaEvaluator(xssfWorkbook);
        } else {
            throw new RuntimeException("文档格式不正确!");
        }
        if (this.wb.getNumberOfSheets() < sheetIndex) {
            throw new RuntimeException("文档中没有工作表!");
        }
        this.sheet = this.wb.getSheetAt(sheetIndex);
        this.headerIndex = headerIndex;
    }

    public <E> List<E> getDataList(Class<E> cls) throws Exception {
        //=========================收集注解数据集=======================
        Field[] fs = cls.getDeclaredFields();
        List<Object[]> annotationList = new ArrayList<>();//Object[0]: @RichExcelField    Object[1]: Field/method
        for (Field field : fs) {
            RichExcelField richExcelField = field.getAnnotation(RichExcelField.class);
            if (richExcelField != null && (richExcelField.type() == 0 || richExcelField.type() == 2)) {
                Map<String, String> selectMap = new HashMap<>();
                annotationList.add(new Object[]{richExcelField.sort(), 1, field, richExcelField, null, selectMap});
            }
        }
        annotationList.sort(Comparator.comparingInt(o -> (int)o[0] ));
        //======================收集导入数据集==========================
        List<E> dataList = new ArrayList<>();

        for (int i = headerIndex + 1; i <= this.sheet.getLastRowNum(); i++) {//遍历数据行
            E e = cls.newInstance();//new 一个反射实例对象
            int column = 0;
            Row row = this.sheet.getRow(i);

            for (Object[] os : annotationList) {//遍历实体注解及字段集合
                Object val;
                if (false && this.isMergedRegion(i, column)) {//合并单元格处理
                    val = this.getMergedRegionValue(i, column);
                } else {//非合并单元格处理
                    val = this.getCellValue(row, column);
                }
                column++;
                if (!StringUtils.isBlank(val.toString())) {
                    if ((int)os[1] == 0) {//ext字段

                    } else {//实体字段
                        RichExcelField richExcelField = (RichExcelField) os[3];
                        Class<?> valType = ((Field) os[2]).getType();
                        try {
                            boolean typeInfer = richExcelField.typeInfer();
                            if (typeInfer) {
                                try {
                                    val = DateUtil.getJavaDate((Double) val);
                                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                                    val = simpleDateFormat.format(val);
                                } catch (Exception a) {
                                    String s = String.valueOf(val.toString());
                                    if (StringUtils.endsWith(s, ".0")) {
                                        val = StringUtils.substringBefore(s, ".0");
                                    } else {
                                        val = String.valueOf(val.toString());
                                    }
                                }
                            } else if (!StringUtils.isEmpty(richExcelField.selectCategory())) {
                                Map<String, String> map = (Map<String, String>) os[5];
                                val = map.get(val);
                            } else {
                                if (valType == String.class) {
                                    String s = String.valueOf(val.toString());
                                    if (StringUtils.endsWith(s, ".0")) {
                                        val = StringUtils.substringBefore(s, ".0");
                                    } else {
                                        val = String.valueOf(val.toString());
                                    }
                                } else if (valType == Integer.class) {
                                    val = Double.valueOf(val.toString()).intValue();
                                } else if (valType == Long.class) {
                                    val = Double.valueOf(val.toString()).longValue();
                                } else if (valType == Double.class) {
                                    val = Double.valueOf(val.toString());
                                } else if (valType == Float.class) {
                                    val = Float.valueOf(val.toString());
                                } else if (valType == Date.class) {
                                    val = DateUtil.getJavaDate((Double) val);
                                } else {
                                    if (richExcelField.fieldType() != Class.class) {
                                        val = richExcelField.fieldType().getMethod("getValue", String.class).invoke(null, val.toString());
                                    } else {
                                        val = Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
                                                "fieldtype." + valType.getSimpleName() + "Type")).getMethod("getValue", String.class).invoke(null, val.toString());
                                    }
                                }
                            }
                        } catch (Exception ex) {
                            if (!StringUtils.isBlank(val.toString())) {
                                throw new Exception("数据错误发生位置 第" + (i + 1) + "行,第" + numberToLetter(column) + "列 错误原因: 出现格式错误请更改！");
                            } else {
                                //log.info("Get cell value [" + (i + 1) + "," + column + "] error: " + ex.toString());
                            }
                            val = null;
                        }
                        //set值到实体对象e
                        Reflections.invokeSetter(e, ((Field) os[2]).getName(), val);
                    }
                }
            }
            dataList.add(e);
        }
        return dataList;
    }

    /**
     * 获取单元格值
     *
     * @param row    获取的行
     * @param column 获取单元格列号
     * @return 单元格值(若为富文本单元格，返回富文本html值)
     */
    public Object getCellValue(Row row, int column) {
        Object val = "";
        try {
            Cell cell = row.getCell(column);
            if (cell != null) {
                if (hssEvaluator != null) {
                    HSSFCell hssfCell = (HSSFCell) cell;
                    if (hssfCell.getCellType() == CellType.FORMULA) {
                        CellValue tempCellValue = hssEvaluator.evaluate(hssfCell);
                        return tempCellValue.getNumberValue();
                    }
                } else {
                    XSSFCell xssfCell = (XSSFCell) cell;
                    if (xssfCell.getCellType() == CellType.FORMULA) {
                        CellValue tempCellValue = xssEvaluator.evaluate(xssfCell);
                        return tempCellValue.getNumberValue();
                    }
                }
                if (cell.getCellType() == CellType.NUMERIC) {
                    val = cell.getNumericCellValue();
                } else if (cell.getCellType() == CellType.STRING) {
                    val = cell.getStringCellValue();
                } else if (cell.getCellType() == CellType.FORMULA) {
                    val = cell.getCellFormula();
                } else if (cell.getCellType() == CellType.BOOLEAN) {
                    val = cell.getBooleanCellValue();
                } else if (cell.getCellType() == CellType.ERROR) {
                    val = cell.getErrorCellValue();
                }
            }
        } catch (Exception e) {
            return val;
        }
        return val;
    }

    /**
     * 数字转字母 1-26 ： A-Z(适用于excel排序顺序)
     *
     * @param num 数字
     * @return 字母
     */
    public static String numberToLetter(int num) {
        if (num <= 0) {
            return null;
        }
        StringBuilder letter = new StringBuilder();
        num--;
        do {
            if (letter.length() > 0) {
                num--;
            }
            letter.insert(0, ((char) (num % 26 + (int) 'A')));
            num = (num - num % 26) / 26;
        } while (num > 0);

        return letter.toString();
    }

    /**
     * 获取合并单元格的值
     *
     * @param row    行
     * @param column 列
     * @return 单元格内容
     */
    public Object getMergedRegionValue(int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = this.sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return getCellValue(this.sheet.getRow(firstRow), firstColumn);
                }
            }
        }
        return null;
    }

    /**
     * 判断指定的单元格是否是合并单元格
     *
     * @param row
     * @param column
     * @return
     */
    public boolean isMergedRegion(int row, int column) {
        int sheetMergeCount = this.sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = this.sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

}
